package godbv2

import (
	"database/sql"
	"errors"
	"fmt"
	"iter"
	"reflect"
	"strings"

	sqlserver "github.com/denisenkom/go-mssqldb"
	"github.com/spf13/cast"
)

// Float64ToString 浮点数转换字符串,length小数点后面几位,默认是3位
func float64ToString(f float64) string {
	fmtStr := fmt.Sprintf("%%.%df", 10)
	ret := strings.TrimRight(fmt.Sprintf(fmtStr, f), "0")
	return strings.TrimRight(ret, ".")
}

func valString(i interface{}) string {
	switch s := i.(type) {
	case float64:
		return float64ToString(s)
	case float32:
		return float64ToString(float64(s))
	default:
		return strings.TrimSpace(cast.ToString(i))
	}
}

type setMapVal interface {
	SetMapValue(data map[string]interface{})
}

type StructFieldSetHandler = func(field *reflect.Value, fieldName string, fieldValueTypeName string)

var structFieldSetHandlers []StructFieldSetHandler

// RegisterStructFieldSetHandler 注册结构体字段设置处理函数
func RegisterStructFieldSetHandler(handler StructFieldSetHandler) {
	if handler == nil {
		return
	}
	structFieldSetHandlers = append(structFieldSetHandlers, handler)
}

// SetValueToStruct 设置值到Struct
func SetValueToStruct(obj interface{}, val map[string]any) error {
	if v, ok := obj.(setMapVal); ok {
		v.SetMapValue(val)
		return nil
	}
	var vp reflect.Value
	if v, ok := obj.(reflect.Value); ok {
		vp = v
	} else {
		vp = reflect.ValueOf(obj)
		if vp.CanInterface() {
			vp = vp.Elem()
		}
	}
	if vp.Type().Kind() != reflect.Struct {
		return errors.New("只能转换内容到结构体")
	}
	rtype := reflect.TypeOf(vp.Interface())
	tp := fieldObj(rtype)
	for k, v := range tp {
		d := vp.FieldByName(k)
		if d.Type().Kind() == reflect.Struct { //如果是结构体则再次解析里面内容
			SetValueToStruct(d, val)
			continue
		}
		switch d.Type().String() {
		case "string":
			d.SetString(strings.TrimSpace(cast.ToString(val[v])))
		case "int8", "int16", "int32", "int", "int64":
			d.SetInt(cast.ToInt64(val[v]))
		case "float32", "float64":
			d.SetFloat(cast.ToFloat64(val[v]))
		case "bool":
			d.SetBool(cast.ToBool(val[v]))
		}
		for _, handler := range structFieldSetHandlers {
			handler(&d, k, d.Type().String())
		}
	}
	return nil
}

// fieldObj 解析对象结构
// @param tp reflect.Type 对象类型
// @param vname string 对象名称
func fieldObj(tp reflect.Type) map[string]string {
	x := tp.NumField()
	var field reflect.StructField
	var db string
	tag := map[string]string{}
	for i := 0; i < x; i++ {
		field = tp.Field(i)
		db = strings.Split(field.Tag.Get("db"), ",")[0]
		if db == "" {
			db = field.Name
		}
		tag[field.Name] = db
	}
	return tag
}

// NewRowsResult 读取查询结果
func NewRowsResult(rows *sql.Rows, sql string, args []interface{}) *QueryResult {
	ret := &QueryResult{
		sql:  sql,
		args: args,
	}
	if rows == nil {
		ret.columns = []string{}
		ret.data = nil
	} else {
		var err error
		ret.columns, err = rows.Columns()
		if err != nil {
			ret.err = err
		} else {
			columnTypes, e := rows.ColumnTypes()
			if e == nil {
				ret.columnTypes = make([]string, len(columnTypes))
				for i, v := range columnTypes {
					ret.columnTypes[i] = v.DatabaseTypeName()
				}
			}
			ret.rows = rows
		}
		ret.passRows() //避免忘记关闭查询结果
	}
	return ret
}

// ErrQueryResult 返回一个查询错误
func ErrQueryResult(err error, db string, sql string, args []interface{}) *QueryResult {
	log.Error("SQL错误 "+err.Error(), "sql", sql, "params", args, "db", db)
	return &QueryResult{
		sql:  sql,
		args: args,
		err:  err,
	}
}

type QueryResult struct {
	columns     []string  //查询字段内容
	columnTypes []string  //字段数据类型
	data        [][]any   //查询结果内容
	datalength  int       //结果长度
	rows        *sql.Rows //查询结果对象
	err         error     //查询错误
	sql         string    //查询的sql
	args        []any     //查询参数
}

func (r *QueryResult) Error() error {
	return r.err
}

func (r *QueryResult) IsEmpty() bool {
	return r.datalength < 1
}

// 解析查询结果
func (r *QueryResult) passRows() {
	if r.rows != nil {
		r.data = make([][]any, 0)
		columnTypes, _ := r.rows.ColumnTypes()
		var uniqueidentifierIndexs []int //mssql UNIQUEIDENTIFIER类型数据坐标ID
		for i, v := range columnTypes {
			if v.DatabaseTypeName() == "UNIQUEIDENTIFIER" {
				uniqueidentifierIndexs = append(uniqueidentifierIndexs, i)
			}
		}
		for r.rows.Next() {
			row := make([]any, len(r.columns))
			for i := range row {
				var ref any
				row[i] = &ref
			}
			err := r.rows.Scan(row...)
			if err != nil {
				r.datalength = 0
				r.rows.Close()
				r.rows = nil
				r.err = err
				return
			}
			for k, v := range row {
				row[k] = *v.(*any)
			}
			for _, index := range uniqueidentifierIndexs {
				value := row[index]
				if value != nil {
					i := sqlserver.UniqueIdentifier{}
					e := i.Scan(value)
					if e == nil {
						row[index] = i.String()
					}
				}
			}
			r.data = append(r.data, row)
		}
		r.datalength = len(r.data)
		r.rows.Close()
		r.err = r.rows.Err() //获取到结果错误
		if r.err != nil {
			log.Error("SQL错误", "sql", r.sql, "params", r.args)
		}
		r.rows = nil
	}
}

// Get 读取某行的指定字段值.columnName表示字段名称，index表示第几行默认第一行，如果结果不存在返回nil
func (r *QueryResult) Get(columnName string, index ...int) any {
	if len(index) < 1 {
		index = []int{0}
	}
	if index[0] >= r.datalength { //超出数据返回nil
		return nil
	}
	for i, v := range r.columns {
		if v == columnName {
			return r.data[index[0]][i]
		}
	}
	return nil
}

// GetMap 读取某行的所有数据.
// index代表第几行默认第一行，返回的map中key是数据字段名称，value是值
func (r *QueryResult) GetMap(index ...int) map[string]any {
	if len(index) < 1 {
		index = []int{0}
	}
	if index[0] >= r.datalength {
		return nil
	}
	ret := make(map[string]any)
	for i, v := range r.columns {
		ret[v] = r.data[index[0]][i]
	}
	return ret
}

// Columns 获取字段列表
func (r *QueryResult) Columns() []string {
	return r.columns
}

// ColumnTypes 字段类型
func (r *QueryResult) ColumnTypes() []string {
	return r.columnTypes
}

// Rows 获取所有数据
func (r *QueryResult) Rows() [][]interface{} {
	return r.data
}

// GetStringMap 获取string类型map
func (r *QueryResult) GetStringMap(index ...int) map[string]string {
	if len(index) < 1 {
		index = []int{0}
	}
	if index[0] >= r.datalength {
		return nil
	}
	ret := make(map[string]string)
	for i, v := range r.columns {
		ret[v] = valString(r.data[index[0]][i])
	}
	return ret
}

// GetCsvData 生成csv数据
func (r *QueryResult) GetCsvData() [][]string {
	var ret = make([][]string, r.datalength+1)
	ret[0] = r.columns
	for i, v := range r.data {
		ret[i+1] = make([]string, len(v))
		for a, k := range v {
			ret[i+1][a] = valString(k)
		}
	}
	return ret
}

// Length 获取结果长度
func (r *QueryResult) Length() int {
	return r.datalength
}

// SQLParams 获取sql及其参数
func (r *QueryResult) SQLParams() (string, []any) {
	return r.sql, r.args
}

// ForEach 循环读取所有数据
// 返回的map中key是数据字段名称，value是值,回调函数中如果返回false则停止循环后续数据
func (r *QueryResult) ForEach(f func(map[string]any) bool) *QueryResult {
	if f == nil {
		return r
	}
	if r.datalength < 1 { //没有数据结果直接返回
		return r
	}
	ret := map[string]any{}
	for j, v := range r.data {
		if j >= r.datalength {
			break
		}
		for i, vv := range r.columns {
			ret[vv] = v[i]
		}
		if !f(ret) {
			break
		}
	}
	return r
}

// Iterator 获取记录游标
func (r *QueryResult) Iterator() iter.Seq[map[string]any] {
	return func(yield func(map[string]any) bool) {
		if len(r.data) < 1 {
			return
		}
		for _, v := range r.data {
			dMap := map[string]any{}
			for i, vv := range r.columns {
				dMap[vv] = v[i]
			}
			if !yield(dMap) {
				return
			}
		}
	}
}

// IteratorWithIndex 返回游标及序号
func (r *QueryResult) IteratorWithIndex() iter.Seq2[int, map[string]any] {
	return func(yield func(int, map[string]any) bool) {
		if len(r.data) < 1 {
			return
		}
		for index, v := range r.data {
			dMap := map[string]any{}
			for i, vv := range r.columns {
				dMap[vv] = v[i]
			}
			if !yield(index, dMap) {
				return
			}
		}
	}
}

// NewGenericResult 结果转换结构体
func NewGenericResult[T any](result *QueryResult) *GenericResult[T] {
	return &GenericResult[T]{
		result: result,
	}
}

type GenericResult[T any] struct {
	result *QueryResult
}

// StructArray 结果转换成结构体数组
func (r *GenericResult[T]) StructArray() ([]T, error) {
	var ts T
	if reflect.TypeOf(ts).Kind() != reflect.Struct {
		return nil, errors.New("只能转换内容到结构体")
	}
	length := r.result.Length()
	if length < 1 {
		return nil, nil
	}
	var ret = make([]T, length)
	it := r.result.IteratorWithIndex()
	for i, row := range it {
		var r T
		SetValueToStruct(&r, row)
		ret[i] = r
	}
	return ret, nil
}

// StructPtrArray 结果转换成结构体指针数组
func (r *GenericResult[T]) StructPtrArray() ([]*T, error) {
	var ts T
	if reflect.TypeOf(ts).Kind() != reflect.Struct {
		return nil, errors.New("只能转换内容到结构体")
	}
	length := r.result.Length()
	if length < 1 {
		return nil, nil
	}
	var ret = make([]*T, length)
	it := r.result.IteratorWithIndex()
	for i, row := range it {
		var r T
		SetValueToStruct(&r, row)
		ret[i] = &r
	}
	return ret, nil
}

// FieldToBaseTypeArray 结果某个字段转换成数组
func (r *GenericResult[T]) FieldToBaseTypeArray(fieldName string) ([]T, error) {
	var ts T
	d := reflect.ValueOf(ts)
	dType := d.Type().String()
	switch dType {
	case "string", "int8", "int16", "int32", "int", "int64", "float32", "float64", "bool":
	default:
		return nil, errors.New("该方法只支持基础数据类型")
	}
	columns := r.result.Columns()
	columnIndex := -1
	for i, column := range columns {
		if column == fieldName {
			columnIndex = i
		}
	}
	if columnIndex < 0 || columnIndex > (len(columns)-1) {
		return nil, errors.New("结果不存在指定字段")
	}
	d = reflect.ValueOf(&ts)
	valueOfTs := d.Elem()
	datas := r.result.Rows()
	var ret = make([]T, len(datas))
	for i, data := range datas {
		val := data[columnIndex]
		switch dType {
		case "string":
			valueOfTs.SetString(strings.TrimSpace(cast.ToString(val)))
		case "int8", "int16", "int32", "int", "int64":
			valueOfTs.SetInt(cast.ToInt64(val))
		case "float32", "float64":
			valueOfTs.SetFloat(cast.ToFloat64(val))
		case "bool":
			valueOfTs.SetBool(cast.ToBool(val))
		}
		ret[i] = ts
	}
	return ret, nil
}

// RowToStruct 结果转换成结构体
func (r *GenericResult[T]) RowToStruct(rowIndex ...int) (T, error) {
	var ts T
	if reflect.TypeOf(ts).Kind() != reflect.Struct {
		return ts, errors.New("只能转换内容到结构体")
	}
	length := r.result.Length()
	if length < 1 {
		return ts, nil
	}
	index := 0
	if len(rowIndex) > 0 {
		index = rowIndex[0]
	}
	if index < 0 || index > length-1 {
		return ts, errors.New("错误的索引值")
	}
	dataMap := r.result.GetMap(index)
	var ret T
	SetValueToStruct(&ret, dataMap)
	return ret, nil
}

// RowToStructPtr 结果转换成结构体指针
func (r *GenericResult[T]) RowToStructPtr(rowIndex ...int) (*T, error) {
	ret, err := r.RowToStruct(rowIndex...)
	if err != nil {
		return nil, err
	}
	return &ret, nil
}

// RowFieldToBaseType 结果某个字段转换成
func (r *GenericResult[T]) RowFieldToBaseType(fieldName string, rowIndex ...int) (T, error) {
	var ts T
	d := reflect.ValueOf(ts)
	dType := d.Type().String()
	switch dType {
	case "string", "int8", "int16", "int32", "int", "int64", "float32", "float64", "bool":
	default:
		return ts, errors.New("该方法只支持基础数据类型")
	}
	columns := r.result.Columns()
	columnIndex := -1
	for i, column := range columns {
		if column == fieldName {
			columnIndex = i
		}
	}
	if columnIndex < 0 || columnIndex > (len(columns)-1) {
		return ts, errors.New("结果不存在指定字段")
	}
	index := 0
	if len(rowIndex) > 0 {
		index = rowIndex[0]
	}
	if index < 0 || index > r.result.Length()-1 {
		return ts, errors.New("错误的索引值")
	}
	d = reflect.ValueOf(&ts)
	valueOfTs := d.Elem()
	data := r.result.Rows()[index]
	val := data[columnIndex]
	switch dType {
	case "string":
		valueOfTs.SetString(strings.TrimSpace(cast.ToString(val)))
	case "int8", "int16", "int32", "int", "int64":
		valueOfTs.SetInt(cast.ToInt64(val))
	case "float32", "float64":
		valueOfTs.SetFloat(cast.ToFloat64(val))
	case "bool":
		valueOfTs.SetBool(cast.ToBool(val))
	}
	return ts, nil
}

// NewExecResult 获取一个操作结果对象
func NewExecResult(rs sql.Result, sql string, args []interface{}) *ExecResult {
	return &ExecResult{
		err:    nil,
		sql:    sql,
		args:   args,
		Result: rs,
	}
}

// ErrExecResult 查询错误结果
func ErrExecResult(err error, db string, sql string, args []interface{}) *ExecResult {
	log.Error("SQL错误 "+err.Error(), "sql", sql, "params", args, "db", db)
	return &ExecResult{
		sql:  sql,
		args: args,
		err:  err,
	}
}

type ExecResult struct {
	sql.Result
	sql  string
	args []interface{}
	err  error //查询错误
}

func (r *ExecResult) Error(reportZeroChange ...bool) error {
	if r.err != nil {
		return r.err
	} else if len(reportZeroChange) < 1 {
		reportZeroChange = []bool{false}
	}
	changrow, _ := r.RowsAffected()
	if changrow == 0 && reportZeroChange[0] {
		return SQLEmptyChange
	}
	return nil
}

// SQLParams 获取sql及其参数
func (r *ExecResult) SQLParams() (string, []any) {
	return r.sql, r.args
}

// LastInsertId returns the integer generated by the database
// in response to a command. Typically this will be from an
// "auto increment" column when inserting a new row. Not all
// databases support this feature, and the syntax of such
// statements varies.
func (r *ExecResult) LastInsertId() (int64, error) {
	if r.Result != nil {
		return r.Result.LastInsertId()
	}
	return 0, r.err
}

// RowsAffected returns the number of rows affected by an
// update, insert, or delete. Not every database or database
// driver may support this.
func (r *ExecResult) RowsAffected() (int64, error) {
	if r.Result != nil {
		return r.Result.RowsAffected()
	}
	return 0, r.err
}
